using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class TVC_CATE1DataAccess : Connection
    {
        #region Local Variable

        #endregion Local Variable

        #region Method
        public TVC_CATE1Info Get(string db,
        String maingroup,
        String subtype,
        String typevalue,
        ref string sErr)
        {
            TVC_CATE1Info objEntr = new TVC_CATE1Info();
            InitConnect();
            InitCommand(@"SELECT     maingroup, subtype, typevalue, typename
                FROM         TVC_CATE1" + db + @"
                WHERE     (maingroup = @maingroup) AND (subtype = @subtype) AND (typevalue = @typevalue)");

            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            AddParameter(TVC_CATE1Info.Field.subtype.ToString(), subtype);
            AddParameter(TVC_CATE1Info.Field.typevalue.ToString(), typevalue);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new TVC_CATE1Info(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, string maingroup, ref string sErr)
        {
            InitConnect();
            string sql = @"SELECT c.maingroup,p.mainname, c.subtype, c.typevalue, c.typename
                FROM TVC_CATE1" + db + @" c 
                    INNER JOIN TVC_CATE2" + db + @" p ON c.maingroup = p.maingroup";

            if (maingroup != null) sql += " WHERE c.maingroup = @maingroup";

            InitCommand(sql);

            if (maingroup != null) AddParameter("maingroup", maingroup);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(TVC_CATE1Info objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO TVC_CATE1" + objEntr.db + @"
                      (maingroup, subtype, typevalue, typename)
                VALUES     (@maingroup,@subtype,@typevalue,@typename)");

            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), objEntr.maingroup);
            AddParameter(TVC_CATE1Info.Field.subtype.ToString(), objEntr.subtype);
            AddParameter(TVC_CATE1Info.Field.typevalue.ToString(), objEntr.typevalue);
            AddParameter(TVC_CATE1Info.Field.typename.ToString(), objEntr.typename);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(TVC_CATE1Info objEntr)
        {
            openConnection();
            InitCommand(@"UPDATE    TVC_CATE1" + objEntr.db + @"
                    SET              typename = @typename
                WHERE     (maingroup = @maingroup) AND (subtype = @subtype) AND (typevalue = @typevalue)");

            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), objEntr.maingroup);
            AddParameter(TVC_CATE1Info.Field.subtype.ToString(), objEntr.subtype);
            AddParameter(TVC_CATE1Info.Field.typevalue.ToString(), objEntr.typevalue);
            AddParameter(TVC_CATE1Info.Field.typename.ToString(), objEntr.typename);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db,
        String maingroup,
        String subtype = null,
        String typevalue = null
        )
        {
            openConnection();
            string sql = @"DELETE FROM TVC_CATE1" + db + @"
                WHERE     (maingroup = @maingroup)";
            if (subtype != null) sql += " AND (subtype = @subtype)";
            if (typevalue != null) sql += " AND (typevalue = @typevalue)";
            InitCommand(sql);
            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            if (subtype != null) AddParameter(TVC_CATE1Info.Field.subtype.ToString(), subtype);
            if (typevalue != null) AddParameter(TVC_CATE1Info.Field.typevalue.ToString(), typevalue);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }



        public Boolean IsExist(string db,
        String maingroup,
        String subtype,
        String typevalue
        )
        {
            InitConnect();
            InitCommand(@"SELECT  TOP(1)   maingroup
                FROM         TVC_CATE1" + db + @"
                WHERE     (maingroup = @maingroup) AND (subtype = @subtype) AND (typevalue = @typevalue)");

            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            AddParameter(TVC_CATE1Info.Field.subtype.ToString(), subtype);
            AddParameter(TVC_CATE1Info.Field.typevalue.ToString(), typevalue);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }


        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            return GetAll(dtb, null, ref sErr);
        }
        #endregion Method


        public DataTable GetItemCate(ref string sErr)
        {
            InitConnect();
            InitCommand(@"SELECT A.[maingroup], A.[maingroup] + A.[typevalue] AS ItemCate, B.mainname + ' - ' + A.typename as CateName FROM [TVC_CATE1HL] as A INNER JOIN [TVC_CATE2HL] AS B ON A.maingroup = B.maingroup WHERE A.subtype=1 ORDER BY ItemCate");
                        
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetSubCode1(string maingroup, ref string sErr)
        {
            InitConnect();
            string Sqlquery = @"SELECT [maingroup], [typevalue], [typename] FROM [TVC_CATE1HL] WHERE [subtype]=2 ";
            if (maingroup != "") Sqlquery += " AND [maingroup] = @maingroup ";

            Sqlquery += " ORDER BY [maingroup], [typevalue] ";
            InitCommand(Sqlquery, CommandType.Text);
            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetSubCode2(string maingroup, ref string sErr)
        {
            InitConnect();
            string Sqlquery = @"SELECT [maingroup], [typevalue], [typename] FROM [TVC_CATE1HL] WHERE [subtype]=3 ";
            if (maingroup != "") Sqlquery += " AND [maingroup] = @maingroup ";

            Sqlquery += " ORDER BY [maingroup], [typevalue] ";
            InitCommand(Sqlquery, CommandType.Text);
            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public bool Checkexistsub(string db, string maingroup, string SubCate)
        {
            InitConnect();
            InitCommand(@"SELECT count(*) AS n FROM
                    (SELECT A.maingroup, A.codesize + B.codelength AS SubCate FROM
                    (SELECT maingroup,typevalue as codesize FROM TVC_CATE1" + db + "  WHERE subtype=2) AS A FULL JOIN " +
                    "(SELECT maingroup,typevalue as codelength FROM TVC_CATE1" + db + " WHERE subtype=3) AS B ON A.maingroup=B.maingroup) AS ABC " +
                    " WHERE ABC.maingroup=@maingroup AND ABC.SubCate=@SubCate");
            AddParameter(TVC_CATE1Info.Field.maingroup.ToString(), maingroup);
            AddParameter("SubCate", SubCate);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (int.Parse(list.Rows[0]["n"].ToString()) > 0)
                return true;
            return false;
        }
    }
}
